{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !conda install ipython-sql -y\n",
    "# !conda install cx-Oracle -y"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "quan =\n",
    "  (DESCRIPTION =\n",
    "    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))\n",
    "    (CONNECT_DATA =\n",
    "      (SERVER = DEDICATED)\n",
    "      (SERVICE_NAME = xe)\n",
    "    )\n",
    "  )"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "acc=\n",
    "  (DESCRIPTION=\n",
    "    (ADDRESS=\n",
    "      (PROTOCOL=TCP)\n",
    "      (HOST=\t172.17.0.3)\n",
    "      (PORT=1521)\n",
    "    )\n",
    "    (CONNECT_DATA=\n",
    "      (SERVICE_NAME=xe)\n",
    "    )\n",
    "  )"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLNET.AUTHENTICATION_SERVICES= (ALL)\n",
    "NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "export TNS_ADMIN=${ORACLE_HOME}/network/admin/sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import cx_Oracle \n",
    "\n",
    "#连接数据库\n",
    "\"用户名/密码@Oracle服务器IP/Oracle的SERVICE_NAME\"\n",
    "conn=cx_Oracle.connect(user='quanwei',\n",
    "                       password='021009',\n",
    "                       dsn='localhost:1521/xe',\n",
    "                       encoding='UTF-8')\n",
    "#建立cursor指针（下面操作都通过此指针进行）\n",
    "cur=conn.cursor()\n",
    "#操作数据库（SQL语句不需要；号）\n",
    "exe=cur.execute('select * from v$database')\n",
    "#读取数据（数据使用元组的方式存储，取数据参考元组数据类型）\n",
    "#一次取一行数据\n",
    "fet=exe.fetchone()\n",
    "print(fet)\n",
    "# 一次取完所有数据\n",
    "fet=exe.fetchall()\n",
    "print(fet)\n",
    "#关闭光标\n",
    "cur.close()\n",
    "#参考数据库版本\n",
    "print(conn.version)\n",
    "#关闭数据库连接\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "sqlalchemy.create_engine('oracle://quanwei/021009@localhost:1521/xe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%sql oracle://quanwei:021009@localhost:1521/xe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students = %sql select * from QUANWEI.student\n",
    "type(students)\n",
    "print(len(students))\n",
    "print(students[0], type(students[0]))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = %sql select * from QUANWEI.student\n",
    "df = x.DataFrame()\n",
    "type(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "create table std(\n",
    "id number,\n",
    "name varchar2(255)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "insert into std values(1,'ArithmeticError')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select * from std\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "drop table std"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "select empno,ename\n",
    "  from scott.emp\n",
    "  where sal>1000\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (1486455240.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Cell \u001b[0;32mIn[1], line 1\u001b[0;36m\u001b[0m\n\u001b[0;31m    cd ${ORACLE_HOME}/network/admin/samples\u001b[0m\n\u001b[0m       ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "cd ${ORACLE_HOME}/network/admin/samples\n",
    "\n",
    "show parameter remote_login_passwordfile\n",
    "\n",
    "alter system set REMOTE_LOGIN_PASSWORDFILE=none scope=spfile\n",
    "\n",
    "show parameters os_authent\n",
    "\n",
    "create user ops$oracle identified externally;\n",
    "\n",
    "grant connect to ops$oracle;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
